package com.java1234.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.java1234.model.Book;
import com.java1234.util.StringUtil;

/**
 * 图书dao类
 * @author Administrator
 *
 */
public class BookDao {

	public int add(Connection con , Book book)throws Exception{
		String sql="insert into t_book values(null, ?,?,?,?,?,?)";
		PreparedStatement pst = con.prepareStatement(sql);
		pst.setString(1, book.getBookName());
		pst.setString(2, book.getAuthor());
		pst.setString(3, book.getSex());
		pst.setFloat(4, book.getPrice());
		pst.setInt(5,book.getBookTypeId());
		pst.setString(6, book.getBookDesc());
		return pst.executeUpdate();
	}
	public ResultSet list(Connection con, Book book)throws Exception{
		StringBuffer sb=new StringBuffer("select * from t_book b, t_bookType bt where b.bookTypeId = bt.id");
		//book外键 = booktype 主键
		if(StringUtil.isNotEmpty(book.getBookName())){
			sb.append(" and b.bookName like '%"+book.getBookName()+"%'");		
		}
		if(StringUtil.isNotEmpty(book.getAuthor())){
			sb.append(" and b.author like '%" +book.getAuthor()+" %' ");		
		}
		if(book.getBookTypeId()!= null && book.getBookTypeId()!=-1 ){
			//=-1是干啥的
			sb.append(" and b.bookTypeId = "+book.getBookTypeId());
		}
		//PreparedStatement pst = con.prepareStatement(sb.toString().replace("and", "where"));
		PreparedStatement pst = con.prepareStatement(sb.toString());
		
		return pst.executeQuery();
	}
	
	
	/**
	 * 图书信息 删除
	 * @param con
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public int delete(Connection con, String id)throws Exception{
		String sql = "delete from t_book where id=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1,id);
		return pstmt.executeUpdate();
		
	}
	
	/**
	 * 图书信息修改
	 * @param con
	 * @param book
	 * @return
	 * @throws Exception
	 */
	public int update(Connection con,Book book) throws Exception{
		String sql = "update t_book set bookName=?,author=?, sex=?,price=?,bookDesc=?,bookTypeId=? where id = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1,book.getBookName());
		pstmt.setString(2, book.getAuthor());
		pstmt.setString(3,book.getSex());
		pstmt.setFloat(4, book.getPrice());
		pstmt.setInt(6, book.getBookTypeId());
		pstmt.setInt(7, book.getId());
		return pstmt.executeUpdate();
	}
	
	/**
	 * 指定图书类别下是否有图书
	 * @param con
	 * @param bookTypeId
	 * @return
	 * @throws Exception 
	 */
	public boolean existBookByBookTypeId(Connection con, String bookTypeId) throws Exception{
		String sql = "select * from t_book where bookTypeId = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, bookTypeId);
		
		ResultSet rs = pstmt.executeQuery();
		return rs.next();
	}
	
}
